﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DuLieu;
using System.Data.OleDb;

namespace DAO
{
    public static class DuongDAO
    {
        // Insert Street into Database.
        public static Boolean InsertDuong(DuongDTO duong)
        {
            DuongDTO newDuong = GetDuongByID(duong.MaDuong);

            if (newDuong != null)
                return false;

            try
            {
                DataProvider.OpenConnection();

                string insertCmd = "INSERT INTO Duong(MaDuong, TenDuong) values(" + duong.MaDuong + "," + "'" + duong.TenDuong + "'" + ")";
                DataProvider.ExecuteNonQuery(insertCmd);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return true;
        }

        // Delete Street into Database.
        public static Boolean DeleteDuong(int id)
        {
            DuongDTO delDuong = GetDuongByID(id);

            if (delDuong == null)
                return false;

            try
            {
                DataProvider.OpenConnection();
                
                string delCmd = "DELETE FROM WHERE MaDuong = " + id;
                DataProvider.ExecuteNonQuery(delCmd);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return true;
        }

        // Update Street into Database.
        public static Boolean UpdateDuong(DuongDTO duong)
        {
            DuongDTO tempDuong = GetDuongByID(duong.MaDuong);

            if (tempDuong == null)
                return false;

            try
            {
                DataProvider.OpenConnection();

                string updateCmd = "UPDATE Duong SET TenDuong = " + duong.TenDuong + "WHERE MaDuong = " + duong.MaDuong;
                DataProvider.ExecuteNonQuery(updateCmd);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return true;
        }

        // Get all Street from Database.
        public static List<DuongDTO> GetDanhSachDuong()
        {
            List<DuongDTO> resultList = null;

            try
            {
                DataProvider.OpenConnection();
                
                resultList = new List<DuongDTO>();

                string selCmd = "SELECT * FROM Duong";
                OleDbDataReader reader = DataProvider.ExecuteReader(selCmd);

                while (reader.Read())
                { 
                    DuongDTO duong = new DuongDTO();
                    if (!reader.IsDBNull(0))
                        duong.MaDuong = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        duong.TenDuong = reader.GetString(1);

                    resultList.Add(duong);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return resultList;
        }

        // Search street by ID from Database
        public static DuongDTO GetDuongByID(int id)
        {
            DuongDTO result = null;

            try
            {
                DataProvider.OpenConnection();
                String selCmd = "SELECT * FROM Duong WHERE MaDuong = " + id;
                OleDbDataReader reader = DataProvider.ExecuteReader(selCmd);

                if (reader.Read())
                {
                    result = new DuongDTO();
                    if (!reader.IsDBNull(0))
                        result.MaDuong = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        result.TenDuong = reader.GetString(1);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return result;
        }

        public static String GetNameByID(int id)
        {
            String result = null;

            try
            {
                DataProvider.OpenConnection();
                String selCmd = "SELECT TenDuong FROM Duong WHERE MaDuong = " + id;
                OleDbDataReader reader = DataProvider.ExecuteReader(selCmd);

                if (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                        result = reader.GetString(0);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return result;
        }

        // Search street by Name from Database
        public static DuongDTO GetDuongByName(string name)
        {
            DuongDTO result = null;

            try
            {
                DataProvider.OpenConnection();
                String selCmd = "SELECT * FROM Duong WHERE TenDuong = " + name;
                OleDbDataReader reader = DataProvider.ExecuteReader(selCmd);

                if (reader.Read())
                {
                    result = new DuongDTO();
                    if (!reader.IsDBNull(0))
                        result.MaDuong = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        result.TenDuong = reader.GetString(1);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return result;
        }

    }
}
